package pta10;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class StatementTest{
	private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private static String driverName = "com.mysql.jdbc.Driver";
	private static String userName = "root";//root
	private static String password = "87552624xy";
	private static ResultSet rs;
	private static Statement st;
	private static Connection conn=null;
	private static int resultNum = 0;

	public static void closeConnection(Connection conn) {
		System.out.println("正在释放所有资源...");	
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}
	/*
	 * 释放所有资源
	 */
	public static void realeaseAll(ResultSet rs,Statement st,Connection conn){
		if(rs!=null){
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st!=null){
			try {
				st.close();
				st = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		closeConnection(conn);
	}
	//显示所有学生的学号、名字和出生时间，select
	public static void displayAll() throws SQLException{
		String strsql="select id,stuno,name,birthdate from student";
		conn = DriverManager
				.getConnection(url,userName,password);
		st=conn.createStatement();
	    rs=st.executeQuery(strsql);
		System.out.println("学号\t\t姓名\t出生日期");
		while(rs.next()){
			System.out.print(rs.getInt("id")+"\t");
			System.out.print(rs.getString("stuno")+"\t");
			System.out.print(rs.getString("name")+"\t");
			System.out.print(rs.getString("birthdate")+"\t\n");
		}
		realeaseAll(rs, st, conn);
	}   
	public static int insert(Student stu) throws SQLException{
		 conn = DriverManager
					.getConnection(url,userName,password);
			String strsql="insert into student(stuno,name,age,birthdate)"
					+ " values('"+stu.getStuno()+"','"+stu.getName()+"',"+stu.getAge()+",'"+stu.getBirthdate()+"')";
			System.out.println(strsql);
			st=conn.createStatement();
			resultNum=st.executeUpdate(strsql);
		return resultNum;
	}   
	public static void displayAllOrderByIdDesc() throws SQLException{
		conn=DriverManager
				.getConnection(url,userName,password);
		String strsql="select * from student order by id desc";
		st=conn.createStatement();
		rs=st.executeQuery(strsql);
		System.out.println("ID\t\t姓名\t出生日期");
		while(rs.next()){
			System.out.print(rs.getInt("id")+'\t');
			System.out.print(rs.getString("stuno")+'\t');
			System.out.print(rs.getString("name")+'\t');
			System.out.print(rs.getString("birthdate")+"\t\n");
		}
		realeaseAll(rs, st, conn);
	}
	public static int deleteStudent(int id) throws SQLException{
		conn=DriverManager
				.getConnection(url,userName,password);
		String strsql="delete from student where id='"+id+"'";
		st=conn.createStatement();
		resultNum=st.executeUpdate(strsql);
		realeaseAll(rs, st, conn);
		return resultNum;
	}
	//将每个学生的年龄+1
	public static int updateStudentAge() throws SQLException{
		conn=DriverManager
				.getConnection(url,userName,password);
		String strsql="update student set age=age-1 where id";
		st=conn.createStatement();
		resultNum=st.executeUpdate(strsql);
		realeaseAll(rs, st, conn);
		return resultNum;
	}
	public static void main(String[] args) throws SQLException {
		Student stu=new Student("2015015", "zkb", 17,java.sql.Date.valueOf("1997-09-11"));
		int id=5;
		deleteStudent(id);
		displayAll();
	}
}
